package com.creawor.hz_market.t_basestation;

import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Date;
import java.text.SimpleDateFormat;
import java.util.ArrayList;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.WorkbookSettings;
import net.sf.hibernate.HibernateException;
import net.sf.hibernate.Session;
import net.sf.hibernate.SessionFactory;
import net.sf.hibernate.Transaction;
import net.sf.hibernate.cfg.Configuration;

public class t_basestation_importHandle {

	// private static final String ADVERTISEMENT = "t_advertisement";
	String date = null;

	public void setString(String date) {
		this.date = date;
	}

	public t_basestation_importHandle() {

	}

	private Date StringtoDate(String str) {
		if (str == null || "".equals(str))
			return null;
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
		Date date = null;
		try {

			date = Date.valueOf(str);
		} catch (Exception ex) {
			ex.printStackTrace();
			return StringtoDate("1970-01-01");
		}
		return date;
	}

	public void insertData(ArrayList al) {
		try {
			// File propertyFile = new File("hibernate.cfg.xml");
			Configuration conf = null;
			// conf = new Configuration().configure(propertyFile);

			SessionFactory sf = new Configuration().configure()
					.buildSessionFactory();
			// SessionFactory sf= conf.buildSessionFactory();

			Session session = sf.openSession();
			// Session session =HibernateSessionFactory.getSession();
			Transaction tx = session.beginTransaction();

			for (int i = 0; i < al.size(); i++) {
				t_basestation vo = (t_basestation) al.get(i);
				session.save(vo);
			}
			tx.commit();
			session.flush();
			// session.close();
		} catch (HibernateException e) {
			e.printStackTrace();
		}

	}

	public ArrayList readExcel(InputStream is) {
		ArrayList al = new ArrayList();
		try {
			WorkbookSettings workbookSettings = new WorkbookSettings();
			workbookSettings.setEncoding("UTF-8"); // 解决中文乱码
			Workbook rwb = Workbook.getWorkbook(is, workbookSettings);
			Sheet st = rwb.getSheet(0);
			int rownum = st.getRows();
			for (int i = 2; i < rownum; i++) {
				t_basestation ad = new t_basestation();
				Cell[] oldcell = st.getRow(i);
				Cell[] cell = new Cell[8];
				System.arraycopy(oldcell, 0, cell, 0, oldcell.length);

				if (cell[0] != null && cell[0].getContents().length() != 0) {
					ad.setCode(cell[0].getContents());
				} else {
					ad.setCode(" ");
				}
				if (cell[1] != null && cell[1].getContents().length() != 0) {
					ad.setName(cell[1].getContents());
				} else {
					ad.setName(" ");
				}
				if (cell[2] != null && cell[2].getContents().length() != 0) {
					ad.setLocation(cell[2].getContents());
				} else {
					ad.setLocation(" ");
				}
				if (cell[3] != null && cell[3].getContents().length() != 0) {
					ad.setMonth_phone_num(Integer.parseInt(cell[3]
							.getContents()));
				} else {
					ad.setMonth_phone_num(-1);
				}
				if (cell[4] != null && cell[4].getContents().length() != 0) {
					ad.setCounty(cell[4].getContents());
				} else {
					ad.setCounty(" ");
				}
				if (cell[5] != null && cell[5].getContents().length() != 0) {
					ad.setTown(cell[5].getContents());
				} else {
					ad.setTown(" ");
				}
				if (cell[6] != null && cell[6].getContents().length() != 0) {
					ad.setVillage_xz(cell[6].getContents());
				} else {
					ad.setVillage_xz(" ");
				}
				if (cell[7] != null && cell[7].getContents().length() != 0) {
					ad.setVillage_zr(cell[7].getContents());
				} else {
					ad.setVillage_zr(" ");
				}
				ad.setInsert_day(StringtoDate(date));
				al.add(ad);
			}
			// }
			//            
			//            
			rwb.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return al;
	}

	/**
	 * 读取Excel
	 * 
	 * @param filePath
	 */
	public ArrayList readExcel(String filePath) {
		ArrayList al = null;

		try {
			InputStream is = new FileInputStream(filePath);
			al = readExcel(is);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return al;
	}

	// 测试
	public static void main(String[] args) {
		try {
			// 读Excel
			t_basestation_importHandle ex = new t_basestation_importHandle();
			System.out.println("starting....");
			ArrayList al = ex.readExcel("d:\\jizhan.xls");
			for (int i = 0; i < al.size(); i++) {
				t_basestation vo = (t_basestation) al.get(i);
				System.out.println("VO:::" + vo.getCounty());
			}
			// ex.insertData(al);
			// 输出Excel
			// File fileWrite = new File("e:\\abc.xls");
			// fileWrite.createNewFile();
			// OutputStream os = new FileOutputStream(fileWrite);
			// ADHandle.writeExcel(os);
			// 修改Excel
			// ExcelHandle.modifyExcel(new file(""),new File(""));
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

}
